package com.dangdi.show.dao;

import com.sun.corba.se.spi.ior.ObjectKey;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;
import java.util.Map;

public interface ManagerDao {
    //查看所有管理员
    @Select("select * from manager where del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> FindAllManagerList(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from manager where del_status=0 ")
    int FindAllManagerListCount();

    //管理员登录
    @Select("select * from manager where manager_tel=#{manager_tel} and manager_password=#{manager_password} and del_status=0")
    public Map<String, Object> ManagerLogin(@Param("manager_tel") String manager_tel, @Param("manager_password") String manager_password);

    //将管理员加入黑名单
    @Update("update manager set manager_status=#{manager_status} where manager_id=#{manager_id}")
    public void lahei(@Param("manager_id") int manager_id, @Param("manager_status") int status);

    //删除管理员
    @Delete("update manager set del_status=-1 where manager_id=#{manager_id}")
    public void shanchu(@Param("manager_id") int manager_id);

    //查找管理员
    @Select("select * from manager where manager_id=#{manager_id} and del_status=0")
    public Map<String, Object> FindManagerBymanagerId(@Param("manager_id") int manager_id);

    //获取角色
    @Select("select * from role,r where role.manager_id=#{manager_id} and role.role_name=r.r_name")
    public List<Map<String, Object>> FindManagerRoleList(@Param("manager_id") int manager_id);

    //修改头像
    @Update("update manager set manager_photo=#{manager_photo} where manager_id=#{manager_id}")
    void UpdateManagerPhoto(@Param("manager_id") int manager_id, @Param("manager_photo") String manager_photo);

    @Update("update manager set manager_name=#{manager_name} ,manager_tel=#{manager_tel},manager_password=#{manager_password},manager_postion=#{manager_postion},manager_address=#{manager_address},manager_sex=#{manager_sex} where manager_id=#{manager_id} ")
    public void updateManagerInfo(
            @Param("manager_name") String manager_name,
            @Param("manager_tel") String manager_tel,
            @Param("manager_password") String manager_password,
            @Param("manager_postion") String manager_postion,
            @Param("manager_address") String manager_address,
            @Param("manager_sex") String manager_sex,
            @Param("manager_id") int manager_id);

    @Delete("delete from role where role_id=#{role_id}")
    public void shanchuRoleByRoleId(@Param("role_id") int role_id);

    @Select("select * from r")
    public List<Map<String, Object>> FindallRoleList();

    @Insert("insert into role values(null,#{r_name},#{manager_id})")
    void addRoleToObjectManager(@Param("manager_id") int manager_id, @Param("r_name") String r_name);

    @Select("select * from manager where del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} or manager_addtime like #{title})  limit #{page},#{limit}")
    public List<Map<String, Object>> searchManager(@Param("page") int i, @Param("limit") int i1, @Param("title") String title);

    @Select("select count(*) from manager where del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} or manager_addtime like #{title}) ")
    int searchManagerCount(@Param("title") String title);

    @Select("select * from manager where del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} )  limit #{page},#{limit}")
    List<Map<String, Object>> searchManagerChinese(@Param("page") int i, @Param("limit") int i1, @Param("title") String title);

    @Select("select * from manager where del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} )  ")
    int searchManagerChineseCount(@Param("title") String title);

    @Update("update manager set manager_password =#{newPassword1} where manager_id=#{manager_id}")
    void updateThisPassword(@Param("newPassword1") String newPassword1, @Param("manager_id") int manager_id);

    @Update("update manager set manager_tel =#{newPhone} where manager_id=#{manager_id}")
    void updateThisPhoneNum(@Param("newPhone") String newPhone, @Param("manager_id") int manager_id);

    @Insert("insert into manager values(null,#{manager_name},#{manager_tel},#{manager_password},1,#{manager_postion},null,null,null,#{manager_address},null,#{manager_photo},#{manager_sex},null,null,now(),null,0)")
        //  @Insert("insert into manager values(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null)")
    void insertManager(@Param("manager_name") String manager_name, @Param("manager_tel") String manager_tel, @Param("manager_password") String manager_password, @Param("manager_postion") String manager_postion, @Param("manager_address") String manager_address, @Param("manager_photo") String manager_photo, @Param("manager_sex") String manager_sex);

    @Select("select * from r where r_name=#{r_name}")
    public Map<String, Object> FindRoleByR_name(@Param("r_name") String r_name);

    @Insert("insert into r values(null,#{r_name},#{r_explain})")
    public void InsertRole(@Param("r_name") String r_name, @Param("r_explain") String r_explain);

    @Delete("delete from r where r_id=#{r_id}")
    public void shanchuRoleByRId(@Param("r_id") int r_id);

    @Select("select * from p where p_name in (select permissions_name from permissions where role_name=#{r_name}) limit #{page},#{limit}")
    List<Map<String, Object>> FindHaverPermissionListByR_id(@Param("r_name") String r_name, @Param("page") int page, @Param("limit") int limit);

    @Delete("delete from permissions where role_name=#{r_name} and permissions_name=#{p_name}")
    void shanchuPermissionByPermissionId(@Param("p_name") String p_name, @Param("r_name") String r_name);

    @Select("select * from p where p_explain like #{title} and p_name in (select permissions_name from permissions where role_name=#{r_name}) limit #{page},#{limit}")
    List<Map<String, Object>> searchPermission(@Param("title") String title, @Param("r_name") String r_name, @Param("page") int page, @Param("limit") int limit);

    @Select("select * from p where p_name not in (select permissions_name from permissions where role_name=#{r_name}) limit #{page},#{limit}")
    List<Map<String, Object>> FindNotHaverPermissionListByR_id(@Param("r_name") String r_name, @Param("page") int page, @Param("limit") int limit);

    @Insert("insert into permissions values(null,#{p_name},#{r_name},'没有备注')")
    void addPermissionToRole(@Param("p_name") String p_name, @Param("r_name") String r_name);

    @Select("select * from p where p_explain like #{title} and p_name not in (select permissions_name from permissions where role_name=#{r_name}) limit #{page},#{limit}")
    List<Map<String, Object>> searchPermission2(@Param("title") String title, @Param("r_name") String r_name, @Param("page") int page, @Param("limit") int limit);

    @Insert("insert into record values(null,#{record_type},#{manager_id},now(),#{record_object},#{record_before},#{record_after},#{record_object_id},#{record_remarks})")
    void insertRecord(@Param("record_type") String record_type, @Param("manager_id") String manager_name, @Param("record_object") String record_object, @Param("record_before") String record_before, @Param("record_after") String record_after, @Param("record_object_id") String record_object_name, @Param("record_remarks") String record_remarks);

    @Select("select * from r where r_name=(select role_name from role where role_id=#{role_id})")
    public Map<String, Object> FindRByRoleId(@Param("role_id") int role_id);

    @Select("select * from r where r_id=#{r_id}")
    public Map<String, Object> FindRByRId(@Param("r_id") int r_id);

    @Select("select * from p where p_name=#{p_name}")
    public Map<String, Object> FindPByPname(@Param("p_name") String p_name);

    @Select("select * from r where r_name=#{r_name}")
    public Map<String, Object> FindRByRname(@Param("r_name") String r_name);

    @Select("select * from record limit #{page},#{limit}")
    public List<Map<String, Object>> FindAllRecordList(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from record ")
    int FindAllRecordListCount();

    @Select("select * from record where record_datetime > #{adate} and record_datetime< #{ddate} and (record_type like #{title} or manager_name like #{title} or record_object like #{title} or record_object_name like #{title} or record_remarks like #{title})  limit #{page},#{limit}")
    List<Map<String, Object>> searchRecord(@Param("page") int i, @Param("limit") int i1, @Param("title") String title, @Param("adate") String adate, @Param("ddate") String ddate);

    @Select("select * from record where record_type like #{title} or manager_name like #{title} or record_object like #{title} or record_object_name like #{title} or record_remarks like #{title}  limit #{page},#{limit}")
    List<Map<String, Object>> searchRecordNoDate(@Param("page") int i, @Param("limit") int limit, @Param("title") String s);

    @Select("select *,#{r_name} as role from manager where manager_id in (select manager_id from role where role_name=#{r_name}) and del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> findManagerByRName(@Param("r_name") String r_name, @Param("page") int page, @Param("limit") int limit);

    @Delete("delete from  role where role_name=#{role_name} and manager_id=#{manager_id}")
    int quxiaoManagerRole(@Param("manager_id") int manager_id, @Param("role_name") String role_name);

    @Select("select * from province  limit #{page},#{limit}")
    List<Map<String, Object>> getProvinceList(@Param("page") int page, @Param("limit") int limit);

    @Select("select * from city  limit #{page},#{limit}")
    List<Map<String, Object>> getCityList(@Param("page") int page, @Param("limit") int limit);

    @Select("select * from area  limit #{page},#{limit}")
    List<Map<String, Object>> getAreaList(@Param("page") int page, @Param("limit") int limit);

    @Select("select * from province where province_name like #{shengfen}  limit #{page},#{limit}")
    List<Map<String, Object>> searchShengfen(@Param("shengfen") String shengfen, @Param("page") int page, @Param("limit") int limit);

    @Select("select * from city where city_name like #{city}  limit #{page},#{limit}")
    List<Map<String, Object>> searchChengshi(@Param("city") String city, @Param("page") int page, @Param("limit") int limit);

    @Select("select * from area where area_name like #{area}  limit #{page},#{limit}")
    List<Map<String, Object>> searchArea(@Param("area") String area, @Param("page") int page, @Param("limit") int limit);

    @Delete("delete from province where province_id=#{province_id} ")
    int shanchuShengfen(@Param("province_id") int province_id);

    @Delete("delete from city where city_id=#{city_id} ")
    int shanchuChengshi(@Param("city_id") int province_id);

    @Delete("delete from area where area_id=#{area_id} ")
    int shanchuQuyu(@Param("area_id") int area_id);

    @Insert("insert into province values(null,#{shengfen})")
    int addShengfen(@Param("shengfen") String shengfen);

    //    @Select("select count(*) from province where province_name=#{shengfen}")
    @Insert("insert into city values(null,#{city_name},#{province_id})")
    int addCity(@Param("province_id") int province_id, @Param("city_name") String city_name);

    @Select("select count(*) from province where province_name=#{shengfen}")
    int check_shengfen(@Param("shengfen") String shengfen);

    @Select("select count(*) from city where city_name=#{city}")
    int check_city(@Param("city") String city);

    @Select("select * from city where province_id=#{province_id}  limit #{page},#{limit}")
    List<Map<String, Object>> filterChengshi(@Param("province_id") int province_id, @Param("page") int page, @Param("limit") int limit);

    @Select("select * from area where city_id=#{city_id}  limit #{page},#{limit}")
    List<Map<String, Object>> filterArea(@Param("city_id") int city_id, @Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from area where area_name=#{area}")
    int check_area(@Param("area") String area);

    @Insert("insert into area values(null,#{area_name},#{city_id})")
    int addArea(@Param("city_id") int city_id, @Param("area_name") String area_name);

    @Delete("delete from index_side_img where index_side_id=#{index_side_id}")
    int delete_lunbo_img(@Param("index_side_id") int index_side_id);

    @Update("update index_side_img set index_side_paper=index_side_paper-1 where index_side_paper>#{index_side_id}")
    int delete_lunbo_img2(@Param("index_side_id") int index_side_id);

    @Insert("insert into index_side_img values(null,#{index_side_img_path},#{index_side_paper})")
    int upload_index_lunbo_img(@Param("index_side_img_path") String index_side_img_path, @Param("index_side_paper") int index_side_paper);

    @Select("select count(*) from index_side_img")
    int index_lunbo_count();

    @Select("select * from index_side_img where index_side_id=#{index_side_id}")
    Map<String, Object> findLunboImgById(@Param("index_side_id") int index_side_id);

    @Update("update index_side_img set index_side_img_path=#{index_side_img_path} where index_side_paper=#{index_side_paper}")
    int update_index_lunbo_img(@Param("index_side_img_path") String index_side_img_path, @Param("index_side_paper") int index_side_paper);

    @Insert("insert into index_haibao_img values(null,#{index_haibao_img_path})")
    int upload_index_haibao_img(@Param("index_haibao_img_path") String index_haibao_img_path);

    @Select("select index_haibao_img_path from index_haibao_img limit 1")
    String getIndexHaiBaoTu();

    @Delete("delete from index_haibao_img")
    int delete_haibao_tu();

    @Select("select * from index_huodong_img limit 1")
    Map<String, Object> getIndexHuodong();

    @Delete("delete from index_huodong_img")
    int delete_huodong();

    //修改活动图片
    @Update("update index_huodong_img set index_huodong_img=#{index_huodong_img} ,index_huodong_title=#{index_huodong_title}")
    int upload_index_huodong_img(@Param("index_huodong_img") String index_huodong_img, @Param("index_huodong_title") String index_huodong_title);

    //添加活动
    @Insert("insert into index_huodong_img values(null,#{index_huodong_img},#{index_huodong_title})")
    int upload_index_huodong_img2(@Param("index_huodong_img") String index_huodong_img, @Param("index_huodong_title") String index_huodong_title);

    //删除活动
    @Delete("delete from index_huodong_img")
    void deleteHuoDong();

    //获取节目列表
    @Select("select *,date_format(pd_date,'20%y-%c-%d %H:%i:%s')as dt from publish left join manager on publish.manager_id=manager.manager_id where publish.del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getPublishList(@Param("page") int page, @Param("limit") int limit);

    //获取首页场景
    @Select("select *,date_format(pd_date,'20%y-%c-%d %H:%i:%s')as dt from scene left join manager on scene.manager_id=manager.manager_id where scene.del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getSceneList(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from publish left join manager on publish.manager_id=manager.manager_id where publish.del_status=0 ")
    int getPublishListCount();

    @Select("select count(*) from scene left join manager on scene.manager_id=manager.manager_id where scene.del_status=0 ")
    int getSceneListCount();

    //删除节目
    @Delete("update publish set del_status=-1 where pd_id=#{pd_id}")
    int deletePublishByPd_id(@Param("pd_id") int pd_id);

    //删除节目
    @Delete("update scene set del_status=-1 where pd_id=#{pd_id}")
    int deleteScenePd_id(@Param("pd_id") int pd_id);

    //获取节目详情
    @Select("select *,date_format(pd_date,'20%y-%c-%d %H:%i:%s')as pd_date,date_format(now(),'20%y-%c-%d %H:%i:%s')as now from publish where pd_id=#{pd_id} and del_status=0")
    Map<String, Object> getPublishInfo(@Param("pd_id") int pd_id);

    //获取场景详情
    @Select("select *,date_format(pd_date,'20%y-%c-%d %H:%i:%s')as pd_date,date_format(now(),'20%y-%c-%d %H:%i:%s')as now from scene where pd_id=#{pd_id} and del_status=0")
    Map<String, Object> getSceneInfo(@Param("pd_id") int pd_id);

    //修改节目信息
    @Update("update publish set pd_title=#{pd_title},pd_content=#{pd_content} where pd_id=#{pd_id}")
    int updatePublishInfo(@Param("pd_id") int pd_id, @Param("pd_title") String pd_title, @Param("pd_content") String pd_content);

    //修改场景信息
    @Update("update scene set pd_title=#{pd_title},pd_content=#{pd_content} where pd_id=#{pd_id}")
    int updateSceneInfo(@Param("pd_id") int pd_id, @Param("pd_title") String pd_title, @Param("pd_content") String pd_content);

    //修改节目图片
    @Update("update publish set pd_image=#{pd_img} where pd_id=#{pd_id}")
    int updatePublishImg(@Param("pd_img") String pd_img, @Param("pd_id") int pd_id);

    //修改节目图片
    @Update("update scene set pd_image=#{pd_img} where pd_id=#{pd_id}")
    int updateSceneImg(@Param("pd_img") String pd_img, @Param("pd_id") int pd_id);

    //添加新节目
    @Insert("insert into publish values(null,#{pd_title},#{pd_content},now(),#{pd_image},null,#{manager_id},0)")
    int addPublish(@Param("pd_title") String pd_title, @Param("pd_content") String pd_content, @Param("pd_image") String pd_image, @Param("manager_id") int manager_id);

    //添加场景
    @Insert("insert into scene values(null,#{pd_title},#{pd_content},now(),#{pd_image},null,#{manager_id},0)")
    int addScene(@Param("pd_title") String pd_title, @Param("pd_content") String pd_content, @Param("pd_image") String pd_image, @Param("manager_id") int manager_id);

    //模糊查询节目
    @Select("select *,date_format(pd_date,'20%y-%c-%d %H:%i:%s')as dt from publish left join manager on publish.manager_id=manager.manager_id where pd_title like #{pd_title} and publish.del_status=0 limit #{page},#{limit} ")
    List<Map<String, Object>> searchPublish(@Param("pd_title") String pd_title, @Param("page") int page, @Param("limit") int limit);

    //模糊查询场景
    @Select("select *,date_format(pd_date,'20%y-%c-%d %H:%i:%s')as dt from scene left join manager on scene.manager_id=manager.manager_id where pd_title like #{pd_title} and scene.del_status=0 limit #{page},#{limit} ")
    List<Map<String, Object>> searchScene(@Param("pd_title") String pd_title, @Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from publish left join manager on publish.manager_id=manager.manager_id where pd_title like #{pd_title} and publish.del_status=0 ")
    int searchPublishCount(@Param("pd_title") String pd_title);

    @Select("select count(*) from scene left join manager on scene.manager_id=manager.manager_id where pd_title like #{pd_title} and scene.del_status=0 ")
    int searchSceneCount(@Param("pd_title") String pd_title);

    //获取收费标准
    @Select("select *  from feeScale left join publish on feeScale.publish_id=publish.pd_id where  publish_id=#{pd_id} and feeScale.del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getFeeScaleByPd_id(@Param("pd_id") int pd_id, @Param("page") int page, @Param("limit") int limit);

    //搜索收费标准
    @Select("select * from feeScale left join publish on feeScale.publish_id=publish.pd_id where  publish_id=#{pd_id} and  feeScale.del_status=0 and (feeScale.publish_time like #{title} or feeScale.publish_price like #{title} or publish.pd_title like #{title}) limit #{page},#{limit}")
    List<Map<String, Object>> searchFeeScale(@Param("pd_id") int pd_id, @Param("title") String title, @Param("page") int page, @Param("limit") int limit);

    //添加收费标准
    @Insert("insert into feeScale values(null,#{pd_id},#{publish_time},#{publish_price},0)")
    int add_feeScale(@Param("pd_id") int pd_id, @Param("publish_time") String publish_time, @Param("publish_price") float publish_price);

    //删除收费标准
    @Delete("update feeScale set del_status=-1 where feeScale_id=#{feeScale_id}")
    int deleteFeaScale(@Param("feeScale_id") int feeScale_id);

    //获取演员列表
    @Select("select *,date_format(actor_add_date,'20%y-%c-%d %H:%i:%S')as a_a_t from actor  left join manager on manager.manager_id=actor.manager_id where actor.del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getActorList(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from actor  left join manager on manager.manager_id=actor.manager_id where actor.del_status=0 ")
    int getActorListCount();

    //模糊查询演员
    @Select("select *,date_format(actor_add_date,'20%y-%c-%d %H:%i:%S')as a_a_t from actor  left join manager on manager.manager_id=actor.manager_id where (actor_name like #{title} or actor_sex like #{title} or actor_tel like #{title} or actor_profession like #{title} or date_format(actor_add_date,'20%y-%c-%d %H:%i:%S') like #{title} or actor_diploma like #{title}) and actor.del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> searchActor(@Param("title") String title, @Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from actor  left join manager on manager.manager_id=actor.manager_id where (actor_name like #{title} or actor_sex like #{title} or actor_tel like #{title} or actor_profession like #{title} or date_format(actor_add_date,'20%y-%c-%d %H:%i:%S') like #{title} or actor_diploma like #{title}) and actor.del_status=0 ")
    int searchActorCount(@Param("title") String title);

    @Select("select * from actor where actor_id=#{actor_id} and del_status=0")
    Map<String, Object> getActorInfo(@Param("actor_id") int actor_id);

    //添加演员
    @Insert("insert into actor values(null,#{actor_name},#{actor_sex},#{actor_tel},#{actor_profession},now(),#{manager_id},#{actor_diploma},#{actor_img},-1,0)")
    int add_actor(Map<String, Object> actor);

    //修改演员信息
    @Update("update actor set actor_name=#{actor_name},actor_sex=#{actor_sex},actor_tel=#{actor_tel},actor_profession=#{actor_profession},actor_diploma=#{actor_diploma} where actor_id=#{actor_id}")
    int update_actor(Map actor);

    //修改演员头像
    @Update("update actor set actor_img=#{actor_img} where actor_id=#{actor_id}")
    int updateActorImg(@Param("actor_img") String actor_img, @Param("actor_id") int actor_id);

    //删除演员
    @Delete("update actor set del_status=-1 where actor_id=#{actor_id}")
    int deleteActor(@Param("actor_id") int actor_id);

    //按照节目id查询演员
    @Select("select *,(select count(*) from actor_publish where actor_publish.actor_id=actor.actor_id and publish_id=#{publish_id}) as ke,date_format(actor_add_date,'20%y-%c-%d %H:%i:%S')as a_a_t from actor where del_status=0 order by ke asc limit #{page},#{limit}")
    List<Map<String, Object>> getActorListByPublish_id(@Param("publish_id") int pd_id, @Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from actor where del_status=0  ")
    int getActorListByPublish_idCount();

    //按照节目id查询搜索演员
    @Select("select *,if(publish_id=#{publish_id},'已分配','可分配')  as st,date_format(actor_add_date,'20%y-%c-%d %H:%i:%S')as a_a_t from actor left join actor_publish on actor_publish.actor_id=actor.actor_id  left join publish on publish.pd_id=actor_publish.publish_id left join manager on manager.manager_id=actor.manager_id where actor_name like #{title} and actor.del_status=0 order by st asc limit #{page},#{limit} ")
    List<Map<String, Object>> searchGetActorListByPublish_id(@Param("title") String title, @Param("publish_id") int pd_id, @Param("page") int page, @Param("limit") int limit);

    //节目取消分配演员
    @Delete("delete from actor_publish where publish_id=#{publish_id} and actor_id=#{actor_id}")
    int cancelActorOfPublish(@Param("publish_id") int publish_id, @Param("actor_id") int actor_id);

    //设置分配演员
    @Insert("insert into actor_publish values(null,#{actor_id},#{publish_id})")
    int setActorPublish(@Param("actor_id") int actor_id, @Param("publish_id") int publish_id);

    //获取支付商品名称
    @Select("select * from properties where properties_name=#{properties_name}")
    Map<String, Object> getProperties(@Param("properties_name") String properties_name);

    //获取新订单
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=1 and del_status=0 order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> getnewOderList(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=1 and del_status=0 ")
    int getnewOderListCount();

    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=2 and del_status=0 order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> getnewOderList2(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=2 and del_status=0 ")
    int getnewOderList2Count();

    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> getnewOderList3(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 ")
    int getnewOderList3Count();

    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=4 and del_status=0 order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> getnewOderList4(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=4 and del_status=0 ")
    int getnewOderList4Count();

    //获取已处理订单
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=2 and del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getResponsedOderList(@Param("page") int page, @Param("limit") int limit);

    //获取完成订单
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getDoneOderList(@Param("page") int page, @Param("limit") int limit);

    //获取退款订单
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> getRefondOderList(@Param("page") int page, @Param("limit") int limit);

    //获取订单的演员
    @Select("select * ,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from  order_feeScale left join feeScale on order_feeScale.feeScale_id=feeScale.feeScale_id left join publish on publish.pd_id=feeScale.publish_id  left join wechat_order on wechat_order.order_id=order_feeScale.order_id left join address on address.address_id=wechat_order.address_id  where order_feeScale.order_id=#{order_id}")
    List<Map<String, Object>> getActorListByOrder_id(@Param("order_id") int order_id);

    //取消订单的演员
    @Update("update order_feeScale set actor_id=null where order_feeScale_id=#{feeScale_id}")
    int cancelActorByFeeScale_id(@Param("feeScale_id") int feeScale_id);

    //获取订单设置演员列表
    @Select("select *, ( select count(*) from order_feeScale_actor where order_feeScale_id=#{order_feeScale_id}  and order_feeScale_actor.actor_id=actor.actor_id )  as ke  from actor_publish left join actor on actor.actor_id=actor_publish.actor_id where publish_id=#{publish_id}  limit #{page},#{limit}")
    List<Map<String, Object>> getActorListSetOrderByPublish_id(@Param("order_feeScale_id") int order_feeScale_id, @Param("publish_id") int publish_id, @Param("page") int page, @Param("limit") int limit);

    //订单安排演员
    @Insert("insert into order_feeScale_actor values(null,#{order_feeScale_id},#{actor_id})")
    int orderSetActorByFeeScale_id(@Param("actor_id") int actor_id, @Param("order_feeScale_id") int order_feeScale_id);

    //
    @Select("select * from actor_publish left join actor on actor_publish.actor_id=actor.actor_id and actor.del_status=0 where publish_id=#{publish_id} and actor_name like #{title} limit #{page},#{limit}")
    List<Map<String, Object>> searchOrderSetActorList(@Param("title") String title, @Param("publish_id") int publish_id, @Param("page") int page, @Param("limit") int limit);

    //获取订单的演员
    @Select("select * from actor left join order_feeScale_actor on order_feeScale_actor.actor_id=actor.actor_id left join actor_publish on actor_publish.actor_id=actor.actor_id left join publish on publish.pd_id=actor_publish.publish_id left join order_feeScale on order_feeScale.order_feeScale_id= order_feeScale_actor.order_feeScale_id where order_feeScale.order_id=#{order_id}")
    List<Map<String, Object>> getActorForFeeScaleByOrder_id(@Param("order_id") int order_id);

    //取消订单的演员
    @Delete("delete from order_feeScale_actor where order_feeScale_id=#{order_feeScale_id} and actor_id=#{actor_id}")
    int cancelActorToOrder(@Param("order_feeScale_id") int order_feeScale_id, @Param("actor_id") int actor_id);

    //更改订单状态
    @Update("update wechat_order set order_status=#{order_status} where order_id=#{order_id}")
    int updateOrderStatusByOrder_id(@Param("order_status") int order_status, @Param("order_id") int order_id);

    //通过订单ID获取客户电话
    @Select("select *,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st from wechat_order left join address on address.address_id=wechat_order.address_id where order_id=#{order_id}")
    Map<String, Object> getUser_telByOrder_id(@Param("order_id") int order_id);

    //通过订单号获取客户电话
    @Select("select *,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st from wechat_order left join address on address.address_id=wechat_order.address_id where order_number=#{order_number}")
    List<Map<String, Object>> getUser_telByOrder_number(@Param("order_number") String order_number);

    //通过订单号获取演员
    @Select("select * from order_feeScale left join order_feeScale_actor on order_feeScale_actor.order_feeScale_id=order_feeScale.order_feeScale_id  left join actor on actor.actor_id=order_feeScale_actor.actor_id  where order_id=#{order_id}")
    List<Map<String, Object>> getOrderActorListByOrder_id(@Param("order_id") int order_id);

    //通过订单id获取订单节目
    @Select("select * from order_feeScale left join feeScale on feeScale.feeScale_id=order_feeScale.feeScale_id  left join publish on publish.pd_id=feeScale.publish_id  where order_id=#{order_id}")
    List<Map<String, Object>> getPublishListByOrder_id(@Param("order_id") int order_id);

    //修改订单responsetime
    @Update("update wechat_order set response_time=now() where order_id=#{order_id}")
    int updateOrderResponseTimeByOrder_id(@Param("order_id") int order_id);

    //修改订单donetime
    @Update("update wechat_order set order_done_time=now() where order_id=#{order_id}")
    int updateOrderDoneTimeByOrder_id(@Param("order_id") int order_id);

    //获取订单节目点播数量
    @Select("select * ,count(*) as value ,pd_title as name  from order_feeScale left join feeScale on feeScale.feeScale_id=order_feeScale.feeScale_id left join publish on publish.pd_id=feeScale.publish_id group by publish_id having value>0")
    List<Map<String, Object>> getOrder_publish_count();

    //获取所有节目的点播次数
    @Select("select *,count(*)as count from publish left join feeScale on publish.pd_id=feeScale.publish_id left join order_feeScale on order_feeScale.feeScale_id=feeScale.feeScale_id  group by publish_id having count>0")
    List<Map<String, Object>> getAllPublish_order_count();

    //按年月份查询订单数量
    @Select("select count(*)as count from wechat_order where  date_format(order_date,'20%y-%c')=#{yearMonth} and order_status>0")
    int getOrderCountByYearAndMonth(@Param("yearMonth") String yearMonth);

    //删除一条order_feeeScale
    @Delete("delete from order_feeScale where order_feeScale_id=#{order_feeScale_id}")
    int deleteOrder_feeScaleByOrder_feeScale_id(@Param("order_feeScale_id") int order_feeScale_id);

    //搜索订单列表
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=#{order_status} and del_status=0 and (order_date like binary  #{title} or order_total_price like binary  #{title} or response_time like binary  #{title} or order_done_time like binary  #{title} or order_number like binary  #{title} or order_pay_date like binary  #{title} or order_tuikuan_time like binary  #{title} or address.user_name like binary  #{title} or address.user_tel like binary  #{title}  or address.user_sheng like binary  #{title} or address.user_shi like binary  #{title} or address.user_qu like binary  #{title} or address.user_add like binary  #{title})  order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> searchOderList(@Param("title") String title, @Param("page") int page, @Param("limit") int limit, @Param("order_status") int order_status);

    @Select("select count(*) from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=#{order_status} and del_status=0 and (order_date like binary  #{title} or order_total_price like binary  #{title} or response_time like binary  #{title} or order_done_time like binary  #{title} or order_number like binary  #{title} or order_pay_date like binary  #{title} or order_tuikuan_time like binary  #{title} or address.user_name like binary  #{title} or address.user_tel like binary  #{title}  or address.user_sheng like binary  #{title} or address.user_shi like binary  #{title} or address.user_qu like binary  #{title} or address.user_add like binary  #{title}) ")
    int searchOderListCount(@Param("title") String title, @Param("order_status") int order_status);

    //获取演员历史订单
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 and order_id in(select order_id from order_feeScale where order_feeScale_id in (select order_feeScale_id from order_feeScale_actor where actor_id=#{actor_id})) order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> getActorLishiOrder(@Param("page") int page, @Param("limit") int limit, @Param("actor_id") int actor_id);

    @Select("select count(*) from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 and order_id in(select order_id from order_feeScale where order_feeScale_id in (select order_feeScale_id from order_feeScale_actor where actor_id=#{actor_id})) ")
    int getActorLishiOrderCount(@Param("actor_id") int actor_id);

    //搜索演员的订单列表
    @Select("select *,case order_status when 0 then '待付款' when 1 then '已付款' when 2 then '已安排' when 3 then '已完成'   end as status,date_format(order_date,'20%y-%c-%d')as d,date_format(order_date,'%H:%i:%s')as t,date_format(response_time,'20%y-%c-%d')as rd,date_format(response_time,'%H:%i:%s')as rt,date_format(show_time,'20%y-%c-%d')as sd,date_format(show_time,'%H:%i')as st,date_format(order_pay_date,'20%y-%c-%d')as pd,date_format(order_pay_date,'%H:%i:%s')as pt,date_format(order_done_time,'20%y-%c-%d')as wd,date_format(order_done_time,'%H:%i:%s')as wt,date_format(order_tuikuan_time,'20%y-%c-%d')as tkd,date_format(order_tuikuan_time,'%H:%i:%s')as tkt from wechat_order left join address on wechat_order.address_id=address.address_id where order_status=3 and del_status=0 and order_id in(select order_id from order_feeScale where order_feeScale_id in (select order_feeScale_id from order_feeScale_actor where actor_id=#{actor_id})) and (order_date like binary  #{title} or order_total_price like binary  #{title} or response_time like binary  #{title} or order_done_time like binary  #{title} or order_number like binary  #{title} or order_pay_date like binary  #{title} or order_tuikuan_time like binary  #{title} or address.user_name like binary  #{title} or address.user_tel like binary  #{title}  or address.user_sheng like binary  #{title} or address.user_shi like binary  #{title} or address.user_qu like binary  #{title} or address.user_add like binary  #{title})  order by st asc limit #{page},#{limit}")
    List<Map<String, Object>> searchActorOderList(@Param("title") String title, @Param("page") int page, @Param("limit") int limit, @Param("actor_id") int actor_id);

    //获取短信提醒的管理员
    @Select("select * from newodermsgtellist left join manager on manager.manager_id=newodermsgtellist.manager_id where del_status=0")
    List<Map<String, Object>> getSendMessageManagerList();

    //获取未设置短信提醒的管理员
    @Select("select * from manager where manager_id not in (select manager_id from newodermsgtellist) and del_status=0 limit #{page},#{limit}")
    List<Map<String, Object>> FindAllManagerList2(@Param("page") int page, @Param("limit") int limit);

    @Select("select count(*) from manager where manager_id not in (select manager_id from newodermsgtellist) and del_status=0 ")
    int FindAllManagerList2Count();

    //搜索
    @Select("select * from manager where manager_id not in (select manager_id from newodermsgtellist) and del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} )  limit #{page},#{limit}")
    List<Map<String, Object>> searchManagerChinese2(@Param("page") int i, @Param("limit") int i1, @Param("title") String title);

    @Select("select count(*) from manager where manager_id not in (select manager_id from newodermsgtellist) and del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} )")
    int searchManagerChinese2Count2(@Param("title") String title);

    @Select("select * from manager where manager_id not in (select manager_id from newodermsgtellist) and del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} or manager_addtime like #{title})  limit #{page},#{limit}")
    public List<Map<String, Object>> searchManager2(@Param("page") int i, @Param("limit") int i1, @Param("title") String title);

    @Select("select count(*) from manager where manager_id not in (select manager_id from newodermsgtellist) and del_status=0 and (manager_name like #{title} or manager_tel like #{title} or manager_postion like #{title} or manager_address like #{title} or manager_addtime like #{title}) ")
    int searchManager2Count2(@Param("title") String title);

    //删除发短信的管理员
    @Delete("delete from newodermsgtellist where newOderMsgTel_manager_id=#{newOderMsgTel_manager_id}")
    int deleteSendMessageManager(@Param("newOderMsgTel_manager_id") int newOderMsgTel_manager_id);

    //删除发短信的管理员
    @Insert("insert into newodermsgtellist values(null,#{manager_id})")
    int addSendMessageManager(@Param("manager_id") int manager_id);
}
